<?php

namespace app\controller;

use app\BaseController;
use app\model\AutoCategory;
use app\model\AutoTableList;
use think\facade\Cache;
use think\facade\Config;
use think\facade\Db;
use think\Request;
use think\helper\Str;

class CreateTableController extends BaseController
{


    public function tableFiltration(Request $request)
    {
        $param = $request->param();

        $filtrationData = dataBooleanFiltration($param);
        if ($param['gntype'] == 'table') {
            return $this->tableFiltrationAction($filtrationData);
        } else if ($param['gntype'] == 'form') {
            return $this->formFiltrationAction($filtrationData);
        }
    }

    /**
     * @param $param
     *
     * @return \think\Response
     */
    public function formFiltrationAction($param)
    {

        $webData = $param['form_templete'];

        $findData = new AutoTableList;
        $findData->appid = $param['code'];
        $findData->title = $param['title'];
        $findData->description = $param['desc'];
        $findData->original_data = $param;
        $findData->filtration_data = $webData;
        $findData->search_data = '';
        $findData->fields_data = $param['table_map'];
        $findData->sql_data = '';
        $findData->category_code = $param['moduleid'];
        $findData->type = $param['gntype'];
        $findData->center_id = $param['appid'];
        $ret = $findData->replace()->save();

        if ($ret != false) {
            return $this->returnJson(1001, '保存数据成功');
        } else {
            return $this->returnJson(1002, '保存失败');

        }
    }

    /**
     * 获取table数据，对数据进行过滤
     *
     * @param $param
     *
     * @return \think\Response
     */
    public function tableFiltrationAction($param)
    {
        //        $param = $this->mokeData();
        $fieldsData = [];//--表需要查询的字段
        $whereData = []; //--查询条件
        //--过滤出前端用的数据
        $webData['top']['search'] = $param['viewtop']['searchshow'];
        $webData['top']['type'] = 'expand_button';

        $sql = '';
        $sqlFields = '';
        $mainTable = '';//主表
        $leftTable = '';//字表
        foreach ($param['table_list'] as $key => $tableList) {
            $leftOnStr = '';//关联表的条件
            foreach ($tableList['list'] as $key2 => $value) {
                //--过滤出查询的条件
                if (!empty($value['iswhere']) && $value['iswhere'] == 'true') {
                    $webData['top']['searchlist'][] = ['title' => $value['showname'], 'key' => $value['param_key']];
                    $whereData[] = ['key' => $value['table_alias'] . '.' . $value['column_name'], 'param_key' => $value['param_key']];
                }
                //--是否查询当前列数据
                if (!empty($value['isshow']) && $value['isshow'] == 'true') {
                    $data = [];
                    $data['title'] = $value['column_comment'];
                    $data['key'] = !empty($value['column_alias']) ? $value['column_alias'] : $value['column_name'];
                    $webData['columns'][] = $data;
                    $fieldsData[] = $value['column_name'];

                    //---拼装sql-查询的字段
                    if ($sqlFields != '') {
                        $sqlFields .= ',';
                    }
                    if (!empty($value['column_alias'])) {
                        $sqlFields = $sqlFields . ' ' . $value['table_alias'] . '.' . $value['column_name'] . ' as ' . $value['column_alias'];
                    } else {
                        $sqlFields = $sqlFields . ' ' . $value['table_alias'] . '.' . $value['column_name'];
                    }
                }
                //--关联表组装关联条件
                if (!empty($value['target'])) {
                    foreach ($value['target'] as $k => $val) {
                        if ($leftOnStr != '') {
                            $leftOnStr .= 'AND ';
                        }
                        if (!empty($value['column_alias'])) {
                            $leftOnStr .= $value['table_alias'] . '.' . $value['column_alias'] . '=' . $val['table_alias'] . '.' . $val['column_name'] . ' ';
                        } else {
                            $leftOnStr .= $value['table_alias'] . '.' . $value['column_name'] . '=' . $val['table_alias'] . '.' . $val['column_name'] . ' ';
                        }
                    }
                }
            }
            //--- 拼装主表
            if ($key == 0) {
                $mainTable = ' from ' . $tableList['table_name'] . ' ' . $tableList['alias'];
            } else {
                $leftTable = ' LEFT JOIN ' . $tableList['table_name'] . ' ' . $tableList['alias'] . ' ON ' . $leftOnStr;
            }

        }


        $sql = 'select ' . $sqlFields . $mainTable . $leftTable;

        /*过滤表格操作列的按钮*/
        $tableAction = ['title' => $param['viewbody']['buttontitle'], 'buttonshow' => $param['viewbody']['buttonshow'], 'type' => 'expand'];
        if (!empty($param['viewbody']['buttonlist'])) {

            foreach ($param['viewbody']['buttonlist'] as $value) {
                $data = [];
                $data['name'] = $value['name'];
                $data['props'] = empty($value['props']) ? '' : $value['props'];
                $data['fun'] = '(option,_this) => {' . $value['fun'] . '}';
                $data['click_fun'] = 'custom';
                $tableAction['button'][] = $data;
            }
        }
        $webData['columns'][] = $tableAction;

        /*过滤搜索旁边的按钮*/
        foreach ($param['viewtop']['buttonlist'] as $value) {
            $data = [];
            $data['name'] = $value['name'];
            $data['props'] = empty($value['props']) ? '' : $value['props'];
            $data['click_fun'] = 'custom'; // 附加的
            $data['type'] = 'onClick';// 附加的
            $data['fun'] = '(option,_this) =>{' . $value['fun'] . '}';
            $webData['top']['button'][] = $data;
        }

        $saveData['appid'] = $param['code'];
        $saveData['title'] = $param['title'];
        $saveData['description'] = $param['desc'];
        $saveData['original_data'] = $param;
        $saveData['filtration_data'] = $webData;
        $saveData['search_data'] = $whereData;
        $saveData['fields_data'] = $fieldsData;

        $findData = new AutoTableList;
        $findData->appid = $saveData['appid'];
        $findData->title = $saveData['title'];
        $findData->description = $saveData['description'];
        $findData->original_data = $saveData['original_data'];
        $findData->filtration_data = $saveData['filtration_data'];
        $findData->search_data = $saveData['search_data'];
        $findData->fields_data = $saveData['fields_data'];
        $findData->sql_data = $sql;
        $findData->is_page = $param['ispage'] == 'true' ? 1 : 2;
        $findData->category_code = $param['moduleid'];
        $findData->type = $param['gntype'];
        $findData->center_id = $param['appid'];
        $ret = $findData->replace()->save();

        if ($ret != false) {
            return $this->returnJson(1001, '保存数据成功', [$sql, $fieldsData, $whereData]);
        } else {
            return $this->returnJson(1002, '保存失败', [$sql, $fieldsData, $whereData]);

        }

    }

    /**
     * 获取表格头部数据接口
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getTableColumnList(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['center_id'];
        $category_code = $param['cat_id'];
        $appid = $param['appid'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->find();
        return $this->returnJson(1001, '获取表头数据ok', $info['filtration_data']);
    }

    /**
     * 获取web表单显示的数据
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getFormFiltrationData(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['center_id'];
        $category_code = $param['cat_id'];
        $appid = $param['appid'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->find();
        return $this->returnJson(1001, '保存数据成功', $info['filtration_data']);
    }

    /**
     * web自动生成，保存/新增表单数据
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\PDOException
     */
    public function saveFormData(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['center_id'];
        $category_code = $param['cat_id'];
        $appid = $param['appid'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->value('fields_data');
        if ($info != false) {
            $saveData = [];
            $table = '';
            $infoArr = json_decode($info, JSON_UNESCAPED_UNICODE);
            foreach ($infoArr as $key => $value) {
                foreach ($value['list'] as $k => $val) {
                    $table = $val['table_name'];
                    $saveData[$val['column_name']] = empty($param[$val['param_key']]) ? '' : $param[$val['param_key']];
                }
            }
            if (!empty($param['id'])) {
                $result = Db::table($table)->where('id', $param['id'])->update($saveData);
            } else {
                $result = Db::table($table)->insert($saveData);
            }
            if ($result != false) {
                return $this->returnJson(1001, '更新或者插入成功');
            } else {
                return $this->returnJson(1001, '更新插入失败');
            }
        } else {
            return $this->returnJson(1002, '没有对应的数据');
        }
    }

    /**
     * web获取表单的编辑数据
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getFormEditData(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['center_id'];
        $category_code = $param['cat_id'];
        $appid = $param['appid'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->value('fields_data');
        if ($info != false) {
            $selectData = [];
            $table = '';
            $infoArr = json_decode($info, JSON_UNESCAPED_UNICODE);
            foreach ($infoArr as $key => $value) {
                foreach ($value['list'] as $k => $val) {
                    $table = $val['table_name'];
                    $selectData[$val['column_name']] = $val['param_key'];
                }
            }


            $result = Db::table($table)->where('id',$param['id'])->field($selectData)->find();

            if ($result != false) {
                $result['id'] = $param['id'];
                return $this->returnJson(1001, '获取数据成功',$result);
            } else {
                return $this->returnJson(1001, '获取数据失败');
            }
        } else {
            return $this->returnJson(1002, '没有对应的数据');
        }
    }

    /**
     * 删除数据
     * @param Request $request
     *
     * @return \think\Response
     * @throws \Exception
     */
    public function delFormIdData(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['center_id'];
        $category_code = $param['cat_id'];
        $appid = $param['appid'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->value('fields_data');
        if ($info != false) {
            $selectData = [];
            $table = '';
            $infoArr = json_decode($info, JSON_UNESCAPED_UNICODE);
            foreach ($infoArr as $key => $value) {
                $table = $key;
            }

            if($param['del_field'] == '1'){
                $result = Db::table($table)->where('id',$param['id'])->delete();
            }else if($param['del_field'] == '2'){
                $result = Db::table($table)->where('id',$param['id'])->update(['del_flag'=>1]);
            }else if(!empty($param['del_field'])){
                $result = Db::table($table)->where('id',$param['id'])->update([$param['del_field']=> 1]);
            }

            if ($result != false) {
                return $this->returnJson(1001, '删除数据成功');
            } else {
                return $this->returnJson(1001, '删除失败');
            }
        } else {
            return $this->returnJson(1002, '没有对应的数据');
        }
    }

    /**
     * 表格编辑页面获取原始数据
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getEditTableInfo(Request $request)
    {
        $param = $request->param();
        if (empty($param['appid']) || empty($param['moduleid']) || empty($param['code'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $server_id = $param['appid'];
        $category_code = $param['moduleid'];
        $appid = $param['code'];
        $info = AutoTableList::where('appid', $appid)->where('center_id', $server_id)->find();
        return $this->returnJson(1001, '获取数据ok', $info['original_data']);
    }

    /**
     * 根据配置文件返回对应的数据
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\db\exception\PDOException
     */
    public function apiGetTableList(Request $request)
    {
        $param = $request->param();
        if (empty($param['center_id']) || empty($param['cat_id']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $info = AutoTableList::where('appid', $param['appid'])->where('center_id', $param['center_id'])->find()->toArray();
        $whereStr = '';
        foreach ($info['search_data'] as $key => $value) {
            if (!empty($param[$value['param_key']])) {
                if ($whereStr == '') {
                    $whereStr = ' where ' . $value['key'] . '=' . $param[$value['param_key']];
                } else {
                    $whereStr .= ' AND' . $value['key'] . '=' . $param[$value['param_key']];
                }
            }
        }
        if ($info != false) {
            $sql = $whereStr == '' ? $info['sql_data'] : $info['sql_data'] . $whereStr;

            if ($info['is_page'] == 1) {
                //开启分页模式
                $pageSize = !empty($this->param['pageSize']) ? $this->param['pageSize'] : 10;
                $pageNo = empty($param['pageNo']) ? 1 : $param['pageNo'];
                $c = strrpos($sql, 'from');
                $countSql = 'select count(*) as count ' . Str::substr($sql, $c);
                $countSqlResult = Db::query($countSql);
                $totalRecord = $countSqlResult[0]['count'];
                if ($totalRecord == 0) {
                    return $this->returnPageJson(1001, '返回数据成功', ['data' => [], 'last_page' => 0, 'per_page' => 0, 'total' => 0, 'current_page' => 0]);
                }
                $totalPage = $totalRecord == 0 ? 0 : ceil($totalRecord / $pageNo);
                $firstRow = $pageSize * ($pageNo - 1);
                $sql = $sql . ' limit ' . $firstRow . ',' . $pageSize;
                $list = Db::query($sql);
                return $this->returnPageJson(1001, '返回数据成功', ['data' => $list, 'last_page' => $totalPage, 'per_page' => $pageSize, 'total' => $totalRecord, 'current_page' => $pageNo]);
            } else {
                $list = Db::query($sql);
                return $this->returnJson(1001, '返回数据成功', $list);
            }

        } else {
            return $this->returnJson(1002, '没有查询到配置文件');
        }
    }


    /**
     * 保存分类功能
     *
     * @param Request $request
     *
     * @return \think\Response
     */
    public function saveCategory(Request $request)
    {
        $param = $request->param();
        if (empty($param['appid']) || empty($param['moduleid']) || empty($param['modulename'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $data['title'] = $param['modulename'];
        $data['code'] = $param['moduleid'];
        $data['center_id'] = $param['appid'];
        $mo = new AutoCategory();
        $ret = $mo->replace()->save($data);
        if ($ret != false) {
            return $this->returnJson(1001, '保存成功');
        } else {
            return $this->returnJson(1002, '保存失败');

        }
    }

    /**
     * 分类列表
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function getCategoryList(Request $request)
    {
        $param = $request->param();
        if (empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $mo = new AutoCategory();
        $list = $mo->where('center_id', $param['appid'])->select();
        return $this->returnJson(1001, '获取成功', $list);
    }

    /**
     * 表格的数据
     *
     * @param Request $request
     *
     * @return \think\Response
     * @throws \think\db\exception\DbException
     */
    public function getTableList(Request $request)
    {
        $param = $request->param();
        if (empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $where['center_id'] = $param['appid'];
        if (!empty($param['category_code'])) {
            $where['category_code'] = $param['category_code'];
        }
        $mo = new AutoTableList;
        $list = $mo->where($where)->field('appid,title,description,type,category_code,center_id')->paginate(10, false, [])->toArray();
        //        dump( $mo::getLastSql());
        return $this->returnPageJson(1001, '返回数据成功', $list);
    }

    /**
     * 获取当前库的所有表
     * @return \think\Response
     * @throws \think\db\exception\BindParamException
     * @throws \think\db\exception\PDOException
     */
    public function getDataBaseList()
    {
        $databaseName = Config::get('database.connections.mysql.database');
        $sql = "SELECT TABLE_NAME as value,TABLE_COMMENT as label  FROM information_schema.TABLES WHERE table_schema='$databaseName'";//echo $sql;
        $res = Db::query($sql);
        return $this->returnJson(1001, '获取数据成功', $res);

    }

    public function getTableInfoList(Request $request)
    {
        $param = $request->param();
        if (empty($param['table_name']) || empty($param['table_alias']) || empty($param['appid'])) {
            return $this->returnJson(1002, '缺少参数');
        }
        $tab = Config::get('database.connections.mysql.database');
        $tablename = $param['table_name'];
        $sql = "select column_name,data_type,character_maximum_length,column_comment from information_schema.COLUMNS where table_name = '$tablename'and table_schema ='$tab'";
        $res = Db::query($sql);
        if ($res != false) {
            $result = [];
            foreach ($res as $value) {
                $data = [];
                $data['table_alias'] = $param['table_alias'];
                $data['table_comment'] = $value['column_comment'];
                $data['column_comment'] = $value['column_comment'];
                $data['column_name'] = $value['column_name'];
                $data['table_name'] = $param['table_name'];
                $data['key'] = $param['table_alias'] . '_' . $value['column_comment'];
                $result[] = $data;
            }
            return $this->returnJson(1001, '获取数据成功', $result);
        } else {
            return $this->returnJson(1002, '没有查询到');

        }
    }

    public function test(Request $request)
    {
        //        $str = 'select a,b,c from tab wherew';
        //        $s = 'from';
        //        $c = strrpos($str, $s);
        //        echo $c . '</br>';
        //        echo Str::substr($str, $c);
        //        echo ceil(105 / 10);
        $data['s'] = 1;
        $data['z'] = 1;
        $data['s'] = array('ss' => 'true');
        $data['s']['z']['sw'] = array('ss' => 'true');
        $p = \think\facade\Request::header();
        $p1 = \think\facade\Request::getInput();
        $p2 = \think\facade\Request::getContent();
        $p3 = $request->getContent();
        dump($p);
        dump($p1);
        dump($p2);
        dump($p3);

        echo strtotime(20191028185959);

        echo toDate(strtotime(20191028185959));
        echo (string)url('/wl/user/payCallBack')->domain('www.baidu.com');

    }

    public function mokeData()
    {
        $str = '{
    "code": "storeShopList1",
    "title": "商户门店表",
    "desc": "",
    "subform_code": "",
    "table_list": [{
        "alias": "cnss",
        "list": [{
            "table_comment": "商户门店表",
            "ishidden": "true",
            "iswhere": "false",
            "column_name": "id",
            "column_comment": "门店编号",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_id",
            "table_alias": "cnss"
        }, {
            "isshow": "true",
            "showname": "商户id",
            "table_comment": "商户门店表",
            "ishidden": "true",
            "column_name": "store_id",
            "column_comment": "商户id",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_store_id",
            "table_alias": "cnss"
        }, {
            "isshow": "true",
            "showname": "门店名称",
            "table_comment": "商户门店表",
            "ishidden": "true",
            "iswhere": "true",
            "isnull": "true",
            "param_key": "shop_name",
            "column_name": "shop_name",
            "column_comment": "门店名称",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_shop_name",
            "table_alias": "cnss"
        }, {
            "isshow": "true",
            "showname": "门店地址",
            "table_comment": "商户门店表",
            "ishidden": "true",
            "column_name": "shop_address",
            "column_comment": "门店地址",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_shop_address",
            "table_alias": "cnss"
        }, {
            "isshow": "true",
            "showname": "门店电话",
            "table_comment": "商户门店表",
            "ishidden": "true",
            "column_name": "shop_iphone",
            "column_comment": "门店电话",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_shop_iphone",
            "table_alias": "cnss"
        }, {
            "table_comment": "商户门店表",
            "column_name": "create_by",
            "column_comment": "",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_create_by",
            "table_alias": "cnss"
        }, {
            "table_comment": "商户门店表",
            "column_name": "create_time",
            "column_comment": "",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_create_time",
            "table_alias": "cnss"
        }, {
            "table_comment": "商户门店表",
            "column_name": "update_by",
            "column_comment": "",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_update_by",
            "table_alias": "cnss"
        }, {
            "table_comment": "商户门店表",
            "column_name": "update_time",
            "column_comment": "",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_update_time",
            "table_alias": "cnss"
        }, {
            "table_comment": "商户门店表",
            "column_name": "del_flag",
            "column_comment": "",
            "table_name": "ctc_n_store_shop",
            "key": "cnss_del_flag",
            "table_alias": "cnss"
        }],
        "table_name": "ctc_n_store_shop"
    }],
    "form_type": "update",
    "appid": "ctcard",
    "moduleid": "mike",
    "gntype": "table",
    "viewtop": {
        "searchshow": "true",
        "buttonlist": [{
            "btnvisible": "false",
            "name": "新增",
            "fun_copy": "let data = option\n_this.createAction(\"storeShopForm\")",
            "clicktype": "",
            "funvisible": "false",
            "fun": "let data = option\n_this.createAction(\"storeShopForm\")",
            "props": {
                "ghost": "true",
                "size": "default",
                "shape": "circle",
                "type": "default"
            }
        }]
    },
    "viewbody": {
        "buttonshow": "true",
        "buttontitle": "操作",
        "buttonlist": [{
            "btnvisible": "false",
            "name": "编辑",
            "fun_copy": "let data = option\n_this.editAction(\"storeShopForm\",data)",
            "clicktype": "",
            "funvisible": "false",
            "fun": "let data = option\n_this.editAction(\"storeShopForm\",data)",
            "props": {
                "ghost": "true",
                "size": "large",
                "shape": "",
                "type": "dashed"
            }
        }]
    },
    "viewbottom": {
        "ispage": "true"
    },
    "ispage": "true"
}';
        return json_decode($str, JSON_UNESCAPED_UNICODE);
    }

}
